package com.fone.player.storage;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteStatement;

import com.fone.player.entity.OfflineCache;
import com.fone.player.util.L;

/**
 * 
 * 收藏表适配器
 * 
 * @author wangyang
 * @since 2014年4月29日
 */
class FavouriteDataBaseAdapter implements IFoneDatabase {
	private static final String TAG = FavouriteDataBaseAdapter.class
			.getSimpleName();
	private static final FavouriteDataBaseAdapter mInstance = new FavouriteDataBaseAdapter();
	private OperateDataBaseTemplate mGeneralDataBaseTemplate = OperateDataBaseTemplate
			.getInstance();

	private FavouriteDataBaseAdapter() {
	}

	static FavouriteDataBaseAdapter getInstance() {
		return mInstance;
	}

	public int mergeFavouriteList(List<OfflineCache> offlineCacheList) {
		L.v(TAG, "mergeFavouriteList", "start");
		// 需要更新的集合
		List<OfflineCache> updateOfflineCacheList = new ArrayList<OfflineCache>();
		// 需要添加的集合
		List<OfflineCache> addOfflineCacheList = new ArrayList<OfflineCache>();
		// 需要删除的集合
		List<OfflineCache> deleteOfflineCacheList = new ArrayList<OfflineCache>();

		// 临时表
		HashMap<Long, OfflineCache> offlineCacheMap = new HashMap<Long, OfflineCache>();

		mGeneralDataBaseTemplate.open();
		StringBuilder folderListSql = new StringBuilder("select * from ");
		folderListSql.append(TB_FAVOURITE);
		Cursor cursor = mGeneralDataBaseTemplate.select(folderListSql
				.toString());
		if (cursor != null && cursor.getCount() != 0) {
			while (cursor.moveToNext()) {
				long videoId = cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_VIDEO_ID));
				offlineCacheMap.put(videoId, null);
			}

			for (OfflineCache offlineCache : offlineCacheList) {

				long videoId = offlineCache.getCacheVideoId();

				if (videoId != 0) {

					if (offlineCacheMap.containsKey(videoId)) {
						// 更新
						updateOfflineCacheList.add(offlineCache);

					} else {

						// 添加
						addOfflineCacheList.add(offlineCache);

					}

					offlineCacheMap.remove(videoId);

				} else {
					// 删除(ccid=0)
					deleteOfflineCacheList.add(new OfflineCache());
				}
			}

			// 删除剩余的冗余数据
			Collection<OfflineCache> offlineCollection = offlineCacheMap
					.values();
			deleteOfflineCacheList.addAll(offlineCollection);
			cursor.close();
		} else {
			addOfflineCacheList.addAll(offlineCacheList);
		}

		mGeneralDataBaseTemplate.close();

		int result = 0;
		// 添加
		if (addOfflineCacheList.size() != 0) {

			result = addFavouriteList(addOfflineCacheList);

		}

		// 删除
		if (deleteOfflineCacheList.size() != 0) {

			result = deleteFavouriteList(deleteOfflineCacheList);
		}

		// 更新
		if (updateOfflineCacheList.size() != 0) {

			result = updateFavouriteList(updateOfflineCacheList);
		}

		return result;
	}

	/**
	 * 
	 * 添加收藏条目集合
	 * 
	 * @param offlineCacheList
	 *            收藏条目集合
	 * @return int 插入结果<br>
	 *         1 成功 <br>
	 *         -1 插入异常
	 * 
	 */
	synchronized int addFavouriteList(List<OfflineCache> offlineCacheList) {

		// 创建文件夹sql语句
		StringBuilder sql = new StringBuilder();
		sql.append("insert or ignore into ").append(TB_FAVOURITE).append(" (");
		sql.append(FAVOURITE_CID).append(",");
		sql.append(FAVOURITE_CCID).append(",");
		sql.append(FAVOURITE_VIDEO_ID).append(",");
		sql.append(FAVOURITE_CLID).append(",");
		sql.append(FAVOURITE_NAME).append(",");
		sql.append(FAVOURITE_IMAGE_URL).append(",");
		sql.append(FAVOURITE_EPISODE_NUM).append(",");
		sql.append(FAVOURITE_EPISODE_UPDATE_INFO).append(",");
		sql.append(FAVOURITE_CONTENT_TYPE).append(",");
		sql.append(FAVOURITE_IS_DOWNLOADABLE).append(",");
		sql.append(FAVOURITE_IS_DOWNLOAD).append(",");
		sql.append(FAVOURITE_DETAIL_URL).append(",");
		sql.append(FAVOURITE_XYZPLAY_URL).append(",");
		sql.append(FAVOURITE_DRAMA_URL).append(",");
		sql.append(FAVOURITE_SYNCHRONZIE_TYPE).append(",");
		sql.append(FAVOURITE_REPORT_PAGE).append(",");
		sql.append(FAVOURITE_IS_VIP).append(",");
		sql.append(FAVOURITE_CREATE_TIME);
		sql.append(") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");

		try {
			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();

			SQLiteStatement fileStatement = mGeneralDataBaseTemplate
					.getSQLiteStatement(sql.toString());
			for (OfflineCache offlineCache : offlineCacheList) {

				fileStatement.bindLong(1, offlineCache.getCacheCID());
				fileStatement.bindLong(2, offlineCache.getCacheCCID());
				fileStatement.bindLong(3, offlineCache.getCacheVideoId());
				fileStatement.bindLong(4, offlineCache.getCacheCLID());
				fileStatement.bindString(5, offlineCache.getCacheName());
				fileStatement.bindString(6, offlineCache.getCacheImageUrl());
				fileStatement.bindString(7, offlineCache.getCacheEpisodeNum());
				fileStatement.bindString(8,
						offlineCache.getCacheEpisodeUpdateInfo());
				fileStatement.bindLong(9, offlineCache.getCacheContentType());
				fileStatement.bindLong(10,
						offlineCache.getCacheIsDownloadable() ? 1 : 0);
				fileStatement.bindLong(11,
						offlineCache.getCacheIsDownlaod() ? 1 : 0);
				fileStatement.bindString(12, offlineCache.getCacheDetailUrl());
				fileStatement.bindString(13, offlineCache.getCacheXYZPlayUrl());
				fileStatement.bindString(14, offlineCache.getCacheDramaUrl());
				fileStatement.bindLong(15,
						offlineCache.getCacheSynchronzieType());
				fileStatement.bindLong(16, offlineCache.getCacheReportPage());
				fileStatement
						.bindLong(17, offlineCache.getCacheIsVip() ? 1 : 0);
				fileStatement.bindLong(18, System.currentTimeMillis());
				mGeneralDataBaseTemplate.insert(fileStatement);
			}
			mGeneralDataBaseTemplate.setTransactionSuccessful();

		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
				L.e(TAG, "addFavouriteList", e.getMessage());
			}
			return -1;
		} finally {
			mGeneralDataBaseTemplate.endTransaction();
			mGeneralDataBaseTemplate.close();
		}
		return 1;
	}

	/**
	 * 
	 * 判断收藏是否存在
	 * 
	 * @return boolean 是否收藏<br>
	 *         true 已收藏<br>
	 *         false 未收藏
	 */
	public boolean isFavourite(long videoId) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_FAVOURITE);
		sql.append(" where ");
		sql.append(FAVOURITE_VIDEO_ID);
		sql.append("=");
		sql.append(videoId);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return false;
			}
			if (cursor.getCount() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return false;
	}

	/**
	 * 
	 * 获得收藏集合
	 * 
	 * @return ArrayList<OfflineCache> 收藏集合
	 */
	public ArrayList<OfflineCache> getFavouriteList(int synchronzieType) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_FAVOURITE);
		sql.append(" where ");

		switch (synchronzieType) {
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_ALREADY:
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_WAIT:
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT:
			// 获取对应状态的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(synchronzieType);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ALL_WAIT:
			// 获取所有等待添加和删除的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_WAIT);
			sql.append(" or ");
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_CLIENT_ALL:
			// 过滤已经删除的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("!=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_SERVER_ALL:
			// 所有
		default:
			break;
		}

		// 倒序
		sql.append(" order by ");
		sql.append(FAVOURITE_CREATE_TIME);
		sql.append(" desc");

		ArrayList<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return offlineCacheList;
			}

			// 读取数据
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = new OfflineCache();
				offlineCache.setCacheCID(cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_CID)));

				long ccid = cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_CCID));
				offlineCache.setCacheCCID(ccid);
				offlineCache.setCacheVideoId(cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_VIDEO_ID)));
				offlineCache.setCacheCLID(cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_CLID)));
				offlineCache.setCacheName(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_NAME)));
				offlineCache.setCacheImageUrl(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_IMAGE_URL)));
				offlineCache.setCacheEpisodeNum(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_EPISODE_NUM)));
				offlineCache.setCacheEpisodeUpdateInfo(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_EPISODE_UPDATE_INFO)));
				int contentType = cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_CONTENT_TYPE));
				offlineCache.setCacheContentType(contentType);
				switch (contentType) {
				case OfflineCache.CACHE_CONTENT_TYPE_ANIMATION:
				case OfflineCache.CACHE_CONTENT_TYPE_VARIETY:
				case OfflineCache.CACHE_CONTENT_TYPE_TV:
					offlineCache
							.setCacheHierarchyType(OfflineCache.CACHE_HIERARCHY_FOLDER);
					break;
				default:
					offlineCache
							.setCacheHierarchyType(OfflineCache.CACHE_HIERARCHY_FILE);
					break;
				}
				offlineCache.setCacheIsDownloadable(cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_IS_DOWNLOADABLE)) == 1);
				offlineCache.setCacheIsDownlaod(cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_IS_DOWNLOAD)) == 1);
				offlineCache.setCacheDetailUrl(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_DETAIL_URL)));
				offlineCache.setCacheXYZPlayUrl(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_XYZPLAY_URL)));
				offlineCache.setCacheDramaUrl(cursor.getString(cursor
						.getColumnIndex(FAVOURITE_DRAMA_URL)));
				offlineCache.setCacheSynchronzieType(cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_SYNCHRONZIE_TYPE)));
				offlineCache.setCacheReportPage(cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_REPORT_PAGE)));
				offlineCache.setCacheIsVip(cursor.getInt(cursor
						.getColumnIndex(FAVOURITE_IS_VIP)) == 1);
				offlineCache.setCacheCreateTime(cursor.getLong(cursor
						.getColumnIndex(FAVOURITE_CREATE_TIME)));
				offlineCacheList.add(offlineCache);
			}

		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCacheList;
	}

	/**
	 * 
	 * 获得收藏集合
	 * 
	 * @return ArrayList<OfflineCache> 收藏集合
	 */
	public int getFavouriteCount(int synchronzieType) {
		L.v(TAG, "getFavouriteCount", "start");
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_FAVOURITE);
		sql.append(" where ");

		switch (synchronzieType) {
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_ALREADY:
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_WAIT:
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT:
			// 获取对应状态的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(synchronzieType);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ALL_WAIT:
			// 获取所有等待添加和删除的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_ADD_WAIT);
			sql.append(" or ");
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_CLIENT_ALL:
			// 过滤已经删除的
			sql.append(FAVOURITE_SYNCHRONZIE_TYPE);
			sql.append("!=");
			sql.append(OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_DELETE_WAIT);
			break;
		case OfflineCache.CACHE_FAVOURITE_SYNCHRONZIE_SERVER_ALL:
			// 所有
		default:
			break;
		}

		Cursor cursor = null;
		int count = 0;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				L.w(TAG, "getFavouriteCount", "cursor=null");
				return 0;
			}

			count = cursor.getCount();

			L.v(TAG, "getFavouriteCount", "count=" + count);

		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return count;
	}

	/**
	 * 
	 * 根据Id删除收藏 <br>
	 * 同步方法
	 * 
	 * @param CID
	 *            节目CID
	 * @return int 操作结果<br>
	 *         1 删除成功<br>
	 *         -1 删除异常<br>
	 */
	public int deleteFavouriteList(List<OfflineCache> offlineCacheList) {
		int result = 1;
		try {

			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				StringBuilder sql = new StringBuilder();
				sql.append(FAVOURITE_VIDEO_ID);
				sql.append("=");
				sql.append("?");
				if (offlineCache != null) {
					mGeneralDataBaseTemplate.delete(TB_FAVOURITE, sql
							.toString(), new String[] { String
							.valueOf(offlineCache.getCacheVideoId()) });
				}

			}
			mGeneralDataBaseTemplate.setTransactionSuccessful();
			mGeneralDataBaseTemplate.endTransaction();
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 删除所有收藏 <br>
	 * 同步方法
	 * 
	 * @return int 操作结果<br>
	 *         1 删除成功<br>
	 *         -1 删除异常<br>
	 */
	public int deleteFavouriteAll() {
		L.v(TAG, "deleteFavouriteAll", "start");
		int result = 1;
		try {
			L.v(TAG, "deleteFavouriteAll", "open start");
			mGeneralDataBaseTemplate.open();
			L.v(TAG, "deleteFavouriteAll", "open end");
			L.v(TAG, "deleteFavouriteAll", "delete start");
			mGeneralDataBaseTemplate.delete(TB_FAVOURITE, null, null);
			L.v(TAG, "deleteFavouriteAll", "delete end");
		} catch (Exception e) {
			String message = "";
			if (e != null) {
				message = e.getMessage();
				e.printStackTrace();
			}
			L.e(TAG, "deleteFavouriteAll", "start error_message=" + message);
			result = -1;
		} finally {
			L.v(TAG, "deleteFavouriteAll", "close start");
			mGeneralDataBaseTemplate.close();
			L.v(TAG, "deleteFavouriteAll", "close end");
		}
		return result;
	}

	/**
	 * 更新收藏集合
	 * 
	 * @param offlineCacheList
	 *            节目集合
	 * @return int 操作结果<br>
	 *         1 更新成功<br>
	 *         -1 更新异常<br>
	 */
	int updateFavouriteList(List<OfflineCache> offlineCacheList) {
		int result = 1;
		try {

			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(FAVOURITE_NAME, offlineCache.getCacheName());
				contentValues.put(FAVOURITE_IMAGE_URL,
						offlineCache.getCacheImageUrl());
				contentValues.put(FAVOURITE_EPISODE_NUM,
						offlineCache.getCacheEpisodeNum());
				contentValues.put(FAVOURITE_EPISODE_UPDATE_INFO,
						offlineCache.getCacheEpisodeUpdateInfo());
				contentValues.put(FAVOURITE_CONTENT_TYPE,
						offlineCache.getCacheContentType());
				contentValues.put(FAVOURITE_DETAIL_URL,
						offlineCache.getCacheDetailUrl());
				contentValues.put(FAVOURITE_XYZPLAY_URL,
						offlineCache.getCacheXYZPlayUrl());
				contentValues.put(FAVOURITE_DRAMA_URL,
						offlineCache.getCacheDramaUrl());
				contentValues.put(FAVOURITE_REPORT_PAGE,
						offlineCache.getCacheReportPage());
				contentValues.put(FAVOURITE_IS_VIP,
						offlineCache.getCacheIsVip());
				contentValues.put(FAVOURITE_IS_DOWNLOADABLE,
						offlineCache.getCacheIsDownloadable());
				StringBuilder where = new StringBuilder();
				where.append(FAVOURITE_VIDEO_ID);
				where.append("=?");
				mGeneralDataBaseTemplate.update(TB_FAVOURITE, contentValues,
						where.toString(), new String[] { String
								.valueOf(offlineCache.getCacheVideoId()) });
			}
			mGeneralDataBaseTemplate.setTransactionSuccessful();
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.endTransaction();
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 更新收藏同步状态
	 * 
	 * @param offlineCacheList
	 *            节目集合
	 * @return int 操作结果<br>
	 *         1 更新成功<br>
	 *         -1 更新异常<br>
	 */
	int updateFavouriteSynchronzieTypeByOfflineCacheList(
			List<OfflineCache> offlineCacheList) {
		int result = 1;
		try {

			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(FAVOURITE_SYNCHRONZIE_TYPE,
						offlineCache.getCacheSynchronzieType());
				StringBuilder where = new StringBuilder();
				where.append(FAVOURITE_VIDEO_ID);
				where.append("=?");
				mGeneralDataBaseTemplate.update(TB_FAVOURITE, contentValues,
						where.toString(), new String[] { String
								.valueOf(offlineCache.getCacheVideoId()) });
			}

		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.setTransactionSuccessful();
			mGeneralDataBaseTemplate.endTransaction();
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 更新下载状态
	 * 
	 * @param offlineCache
	 *            缓存对象
	 * @return int 操作结果<br>
	 *         1 更新成功<br>
	 *         -1 更新异常<br>
	 */
	public int updateSelectedDownloadState(OfflineCache offlineCache) {
		int result = 1;
		try {

			StringBuilder sql = new StringBuilder("update ");
			sql.append(TB_FAVOURITE);
			sql.append(" set ");
			sql.append(FAVOURITE_IS_DOWNLOAD);
			sql.append("=");
			sql.append(offlineCache.getCacheIsDownlaod() ? 1 : 0);
			sql.append(" where ");
			sql.append(FAVOURITE_VIDEO_ID);
			sql.append("=");
			sql.append(offlineCache.getCacheVideoId());
			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.update(sql.toString());

		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

}
